package net.cathackers.devel.scmp.chat.backend.mysql;

import java.io.StringReader;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.io.SAXReader;

import net.cathackers.devel.scmp.auth.interfaces.UserID;
import net.cathackers.devel.scmp.chat.backends.MySQLPoolBackend;
import net.cathackers.devel.scmp.chat.impl.xmpp.messageComponent.OfflineMessage;
import net.cathackers.devel.scmp.config.impl.OfflineStoreConfig;
import net.cathackers.devel.scmp.exceptions.BackendConnectException;
import net.cathackers.devel.scmp.exceptions.BackendException;
import net.cathackers.devel.scmp.xmpp.Packet;

public class MySQLOfflineStoreBackend extends MySQLPoolBackend {

    private String insertStmt_;
    private String deleteStmt_;
    private String selectStmt_;
    private static final String CONNECTION_POOL_NAME = "offlineMessageStore";
    private static final String CONNECTION_POOL_URI = "jdbc:apache:commons:dbcp:"
	    + CONNECTION_POOL_NAME;

    public MySQLOfflineStoreBackend(OfflineStoreConfig config) {
	super(config, CONNECTION_POOL_NAME, CONNECTION_POOL_URI);

	insertStmt_ = "INSERT INTO " + conf_.getTableName()
		+ " (username, delayDate, message) " + "VALUES (?, ?, ?)";
	deleteStmt_ = "DELETE FROM " + conf_.getTableName()
		+ " WHERE username=?";
	selectStmt_ = "SELECT message, delayDate FROM " + conf_.getTableName()
		+ " WHERE username=?";
    }

    public void addOfflineMessage(Packet packet) {
	Connection con = null;
	String username = (packet.getTo()).getUsername();
	String messageAsXML = packet.toXML();
	String delay = (new Date()).toString();

	try {
	    con = getConnection();
	    PreparedStatement stmt = con.prepareStatement(insertStmt_);
	    stmt.setString(1, username);
	    stmt.setString(2, delay);
	    stmt.setString(3, messageAsXML);
	    stmt.executeUpdate();

	} catch (SQLException e) {
	    logger_.debug("Cannot load the driver jdbc:apache:commons:dbcp: :"
		    + e.getMessage());
	} catch (BackendConnectException e) {
	    logger_.error("Error while communicating with backend  "
			+ e.getMessage());
	} finally {
	    try {
		this.killConnection(con);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }

	}

    }

    public boolean deleteOfflineMessages(UserID userID) {
	boolean ret = false;
	Connection con = null;
	String username = userID.getUsername();

	PreparedStatement stmt;
	try {
	    con = getConnection();
	    stmt = con.prepareStatement(deleteStmt_);
	    stmt.setString(1, username);
	    stmt.executeUpdate();

	    ret = true;
	} catch (SQLException e) {
	    logger_.debug("Cannot load the driver jdbc:apache:commons:dbcp: :"
		    + e.getMessage());
	} catch (BackendConnectException e) {
	    logger_.error("Error while communicating with backend  "
			+ e.getMessage());
	} finally {
	    try {
		this.killConnection(con);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }

	}
	return ret;

    }

    public List<OfflineMessage> getMessages(UserID userID) {
	List<OfflineMessage> offlineMessages = new ArrayList<OfflineMessage>();
	SAXReader reader = new SAXReader();
	Document doc = null;
	Connection connection = null;
	String username = userID.getUsername();
	PreparedStatement stmt;

	try {
	    connection = getConnection();
	    stmt = connection.prepareStatement(selectStmt_);
	    stmt.setString(1, username);
	    ResultSet rs = stmt.executeQuery();
	    while (rs.next()) {
		String messageAsXML = rs.getString(1);
		String delayDate = rs.getString(2);

		OfflineMessage offlineMessage = null;
		try {
		    doc = reader.read(new StringReader(messageAsXML));
		} catch (DocumentException e) {
		    logger_.error("error in reading XMLMessage"
			    + e.getMessage());
		}
		offlineMessage = new OfflineMessage(doc, delayDate);
		offlineMessages.add(offlineMessage);

	    }
	    rs.close();
	    if (offlineMessages.isEmpty() == false) {
		// delete offline messages after we get it with the same
		// connection to save the time
		stmt.close();
		stmt = connection.prepareStatement(deleteStmt_);
		stmt.setString(1, username);
		stmt.executeUpdate();
	    }

	} catch (SQLException e) {
	    logger_.debug("Cannot load the driver jdbc:apache:commons:dbcp: :"
		    + e.getMessage());
	} catch (BackendConnectException e) {
	    logger_.error("Error while communicating with backend  "
			+ e.getMessage());
	} finally {
	    try {
		this.killConnection(connection);
	    } catch (BackendException e) {
		logger_.error(e.getMessage());
	    } catch (NullPointerException ex) {
	    }

	}

	return offlineMessages;
    }

}
